Converting a Sybase SQL script to a PostgreSQL script - Mailing list pgsql-sql

From David Rugge
Subject Converting a Sybase SQL script to a PostgreSQL script
Date
Msg-id l03102800b1eaac407da3@[209.86.35.150]
Whole thread Raw
List pgsql-sql
I am trying to use PostgreSQL and The Practical SQL Handbook to learn SQL.
The book comes with a sample database on a CD for their demo version of
SQLAnywhere. They also include a script file with the SQL commands to
create and populate the sample database.  I've managed to make a few minor
changes in the datatypes and syntax of the commands to get most of the
script to work, but psql gives errors on a few sections when I run the
script using \i.  Can anyone explain what I need to do to fix these errors
so that I can get the sample database working?

Here is the create statement for the problematic table:

create table titles

    (title_id char(6) not null,

    title varchar(80) not null,

    type char(12),

    pub_id char(4),

    price money,

    advance money,

    ytd_sales int4,

    contract boolean not null,

    notes varchar(200),

    pubdate date)

;

grant select on titles to public

;



Here is a truncated transcript of the errors:

insert into titles
values ('PC8888', 'Secrets of Silicon
Valley',
'popular_comp', '1389', $20.00, $8000.00, 4095,1,
'Muckraking
reporting on the world''s largest computer hardware and software
manufacturers.',
'06/12/85')
;
ERROR:  parser: parse error at or near
"00"
insert into titles
values ('BU1032', 'The Busy Executive''s Database
Guide',
'business', '1389', $19.99, $5000.00, 4095, 1,
'An overview of
available database systems with emphasis on common business applications.
Illustrated.',
'06/12/85')
;
ERROR:  parser: parse error at or near
"99"

/*  other inserts into titles deleted */

/* I don't understand why
these creates don't work.  They appear to be */
/* syntactically correct...
*/

create view currentinfo (PUB#, TYPE, INCOME,
        AVG_PRICE,
AVG_SALES)
as
select pub_id, type, sum(price * ytd_sales),
  avg(price),
avg(ytd_sales)
from titles
group by pub_id, type
;
ERROR:  parser: parse
error at or near "("

create view cities (Author, Authorcity, Pub,
Pubcity)
as
select au_lname, authors.city, pub_name, publishers.city
from
authors, publishers
where authors.city = publishers.city
;
ERROR:  parser:
parse error at or near "("

create view accounts (title, advance,
gross_sales)
as
select title_id, advance, price * ytd_sales
from
titles
where price > $15
  and advance > $5000
;
ERROR:  parser: parse
error at or near "("
create view categories(Category, Average_Price)
as
select type, avg(price)
from titles
group by type
;
ERROR:  parser: parse
error at or near "("



-David Rugge
http://www.mindspring.com/~davidrugge/index.html



pgsql-sql by date:

Previous
From: Sascha Schumann
Date:
Subject: int/bit fields
Next
From: Jade
Date:
Subject: aggregates